#!/usr/bin/env python2.7
# -*- encoding:utf8 -*-

import sqlanydb

class IQConnect:
    def __init__(self,dbargs):
        self.connect = sqlanydb.connect(
                userid=dbargs['userid'],
                password=dbargs['password'],
                host=dbargs['host'])

    def __del__(self):
        self.connect.close()

    def find_table(self,number_of_days):
        _sql = """
        select distinct cyc_tablename
                from ky_cdr_manager where substr(cdr_tablename,3,8)
                = any(
                    select distinct substr(cdr_tablename,3,8)
                        from ky_cdr_manager
                        where 1=1
                            and datediff(day,fun_convert_time(substr(cdr_tablename,3,8),'DD'),fun_convert_time((select max(substr(cdr_tablename,3,8))
                    from ky_cdr_manager),'DD')) <= {days}
                    and datediff(day,fun_convert_time(substr(cdr_tablename,3,8),'DD'),fun_convert_time((select max(substr(cdr_tablename,3,8))
                from ky_cdr_manager),'DD')) > 0
                    )"""
        _sql = _sql.format(days=number_of_days)
        print _sql
        cur = self.connect.cursor()
        try:
            cur.execute(_sql)
            s = [r[0] for r in cur.fetchall()]
        except:
            s = []
        cur.close()
        return s


    def select_list(self,table_name,file_name):
        _sql = """select top 10000 mdn,area_id,cdr_city_id,substr(s_cycle_date,1,8) f_date from area{table} where mdn_city_id != cdr_city_id and area_id is not null"""
        _sql = _sql.format(table=table_name)
        print _sql
        cur = self.connect.cursor()
        if 1==1:
            cur.execute(_sql)
            with open(file_name,'a') as fd:
                result = cur.fetchmany(10000)
                while result:
                    string_to_write = ''
                    for row in result:
                        string_to_write += ','.join([str(elements).strip() for elements in row])+'\n'
                    fd.write(string_to_write)
                    result = cur.fetchmany(10000)
        else:
            result = []
        cur.close()
        return result


    def get_cfg_area(self):
        _area = dict()
        _city_ids = set()
        _sql = """select area_id, area_name, city_id from kyfx_cfg_area"""
        print _sql
        cur = self.connect.cursor()
        cur.execute(_sql)
        result = cur.fetchall()
        for area_id,area_name,city_id in result:
            _area.setdefault(area_id,'')
            _area[str(area_id)]=area_name.strip() #area dict.
            _city_ids.add(city_id)
        return {'area_dic':_area,'citys':_city_ids}


    def load_table(self,srcfile,table,sep,end_symbol='0x0a',columns_set=tuple()):
        _load_sql = """load table %s (%s) from '%s' escapes off quotes off with checkpoint on """
        column_str = (" '"+sep+"',").join(columns_set)+' '+str(end_symbol)
        _sql = _load_sql % (table, column_str, srcfile)
        print _sql
        #MyLog.info(_sql)
        cur = self.connect.cursor()
        cur.execute(_sql)
        cur.close()
        self.connect.commit()
        return _sql
